IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetPlayersByTeamIDAndSessionID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_GetPlayersByTeamNameAndSessionID]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_GetPlayersByTeamNameAndSessionID]  
@Season INT
,@TeamName VARCHAR(MAX)
AS

DECLARE @Team INT
SELECT @Team=T.ID FROM TEAM T WHERE T.TeamName LIKE '%'+@TeamName +'%'

SELECT C.ID,C.Name,C.Photo FROM Player P INNER JOIN 
Crew C ON C.ID =P.CrewId 
WHERE P.ID IN (
SELECT DISTINCT PTS.PlayerId  FROM PlayerTeamSeason PTS
WHERE PTS.SeasonId =@Season AND PTS.TeamId =@Team)
ORDER BY C.Name ASC

GO


